import datetime

from sqlalchemy import Table, Column, ForeignKey
from sqlalchemy.orm import relation, backref, mapper
from sqlalchemy.types import BigInteger, Integer, String, DateTime, Text, Boolean, Date
from sqlalchemy.dialects.mysql import ENUM

from fidoweb.model.meta import Base

"""
Rules
	1. Tables and Inner relations are sorted in alphabet order, and Inner relations are defined after Tables
	2. About relations:
		For m-m and o-o relations: 'relation' defined in the first element of the relation
		For o-m(m-o) relations: 'relation' defined in the element of 'm' side
"""

"""
Tables
	CardApplication
	Department
	OAPrivateMsg
	OAPrivateTask
	OAUser
	Profession
	SignUpMobile
	User
	UserGroup
	UserNotification
	UserNotificationType
"""

"""
Inner relations
	CardApplication(user) o-o User(application)
	Department(OAUsers) o-m OAUser(department)
	OAPrivateMsg(OAUser) m-o OAUser(privateMsgs)
	OAPrivateTask(OAUser) m-o OAUser(privateTasks)
	User(userGroups) m-m UserGroup(users)
	User(notifications) o-m UserNotification(user)
	UserNotification(type) m-o UserNotificationType(notifications)
"""

"""
Outer relations
	CardApplication(seller) m-o CardSeller(applications)
	Discount(users) m-m User(discounts) (Describing how much a user like the discount)
	Media139MailTypes(users) m-m User(media139MailTypes) (Describing whether a user need a certain kind of Fido Media)
	School(users) m-m User(schools) (Describing what schools a user has been to, including the profession)
	SchoolMapLoc(users) m-m User(schoolMapLocs) (Describing how much a user like the location)
	SchoolMapLocComment(user) m-o User(schoolMapLocComments)
"""

CardApplication_t = Table('cardApplications', Base.metadata,
	Column('id', BigInteger, ForeignKey('users.id'), primary_key = True),
	Column('updateTime', DateTime(timezone = True), index = True),
	Column('cardSeller_id', BigInteger, ForeignKey('cardSellers.id')),
)

Department_t = Table('departments', Base.metadata, 
	Column('id', BigInteger, primary_key = True),
	Column('name', String(32), unique = True),
)

OAPrivateMsg_t = Table('OAPrivateMsgs', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('description', Text),
	Column('createTime', DateTime(timezone = True), index = True),
	Column('user_id', BigInteger, ForeignKey('OAUsers.id')),
)

OAPrivateTask_t = Table('OAPrivateTasks', Base.metadata, 
	Column('id', BigInteger, primary_key = True),
	Column('user_id', BigInteger, ForeignKey('OAUsers.id')),
	Column('name', String(64)),
	Column('description', Text),
	Column('createTime', DateTime(timezone = True), index = True),
	Column('deadline', DateTime(timezone = True), index = True),
	Column('finishTime', DateTime(timezone = True), index = True),
)

OAUser_t = Table('OAUsers', Base.metadata,
	Column('id', BigInteger, ForeignKey('users.id'), primary_key = True),
	Column('department_id', BigInteger, ForeignKey('departments.id')),
)

Profession_t = Table('professions', Base.metadata,
	Column('id', Integer, primary_key = True),
	Column('name', String(64), unique = True),
)

SignUpEmail_t = Table('signUpEmails', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('email',  String(128), unique = True),
	Column('authCode', String(8), index = True),
	Column('updateTime', DateTime(timezone = True), index = True),
)

SignUpMobile_t = Table('signUpMobiles', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('mobile', String(11), unique = True),
	Column('authCode', String(8), index = True),
	Column('updateTime', DateTime(timezone = True), index = True)
)

User_t = Table('users', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('name', String(32), index = True),
	Column('password', String(128)),
	Column('gender', ENUM('male', 'female'), index = True),
	Column('mobile', String(11), unique = True),
	Column('birthday', Date(timezone = True), index = True),
	Column('createTime', DateTime(timezone = True), index = True),
	Column('fidoPoints', Integer, index = True),
	Column('email', String(128), unique = True),
	Column('fidocard', String(19), unique = True),
	Column('updateTime', DateTime(timezone = True), index = True),
	Column('activeTime', Integer, index = True),
)

UserGroup_t = Table('userGroups', Base.metadata,
	Column('id', Integer, primary_key = True),
	Column('name', String(128), unique = True),
	Column('description', String(300)),
	Column('parent_id', Integer, ForeignKey('userGroups.id'))
)

UserNotification_t = Table('userNotifications', Base.metadata,
	Column('id', BigInteger, primary_key = True),
	Column('user_id', BigInteger, ForeignKey('users.id')),
	Column('additionalId', BigInteger, index = True),		# store the id of an object, if necessary
	Column('repeatedTimes', Integer, index = True),			# store how many time that the notification is repeated 
	Column('type_id', Integer, ForeignKey('userNotificationTypes.id')),
	Column('createTime', DateTime(timezone = True), index = True),
	Column('updateTime', DateTime(timezone = True), index = True),
	Column('content', Text),
)

UserNotificationType_t = Table('userNotificationTypes', Base.metadata,
	Column('id', Integer, primary_key = True),
	Column('name', String(64), unique = True),
)

User_UserGroup_t = Table('users_userGroups', Base.metadata,
	Column('user_id', ForeignKey('users.id'), primary_key = True),
	Column('userGroup_id', ForeignKey('userGroups.id'), primary_key = True),
)

class CardApplication(object) :
	def __init__(self, user_id, cardSeller_id) :
		self.id = user_id
		self.cardSeller_id = cardSeller_id
		self.updateTime = datetime.datetime.now()

class Department(object) :
	def __init__(self, name) :
		self.name = name

class OAPrivateMsg(object) :
	def __init__(self, user_id, description) :
		self.user_id = user_id
		self.description = description
		self.createTime = datetime.datetime.now()

class OAPrivateTask(object) :
	def __init__(self, user_id, name, description, deadline) :
		self.name = name
		self.user_id = user_id
		self.description = description
		self.createTime = now
		self.deadline = deadline
		self.finishTime = NULL

class OAUser(object) :
	def __init__(self, id, department_id) :
		self.id = id
		self.department_id = department_id

class Profession(object) :
	def __init__(self, name) :
		self.name = name

class SignUpEmail(object) :
	def __init__(self, email, authCode) :
		self.email = email
		self.authCode = authCode
		self.updateTime = datetime.datetime.now()
		
class SignUpMobile(object) :
	def __init__(self, mobile, authCode) :
		self.mobile = mobile
		self.authCode = authCode
		self.updateTime = datetime.datetime.now()

class User(object) :
	def __init__(self, fidocard, name, password, gender, mobile, birthday, email) :
		self.name = name
		self.password = password
		self.gender = gender
		self.mobile = mobile
		self.birthday = birthday
		self.email = email
		self.createTime = datetime.datetime.now()
		self.fidocard = fidocard
		self.fidoPoints = 0
		self.updateTime = None
		self.activeTime = 0

class UserGroup(object) :
	def __init__(self, name, description, parent_id) :
		self.name = name
		self.description = description
		self.parent_id = parent_id

class UserNotification(object) :
	def __init__(self, user_id, additionalId, type_id, content) :
		self.user_id = user_id
		self.additionalId = additionalId
		self.type_id = type_id
		self.content = content
		self.repeatedTimes = 1
		self.createTime = datetime.datetime.now()
		self.updateTime = datetime.datetime.now()

class UserNotificationType(object) :
	def __init__(self, name) :
		self.name = name

class User_UserGroup(object) :
	def __init__(self, user_id, userGroup_id) :
		self.user_id = user_id
		self.userGroup_id = userGroup_id

from fidoweb.model.map import SchoolMapLocTopic, SchoolMapLocTopicPost
mapper(CardApplication, CardApplication_t, properties = {
	'id' : CardApplication_t.c.id,
	'updateTime' : CardApplication_t.c.updateTime,
	'cardSeller_id' : CardApplication_t.c.cardSeller_id,
	'user' : relation(User, backref = 'application')
})

mapper(Department, Department_t, properties = {
	'id' : Department_t.c.id,
	'name' : Department_t.c.name,
	'OAUsers' : relation(OAUser, backref = 'department'),
})

mapper(OAPrivateMsg, OAPrivateMsg_t, properties = {
	'id' : OAPrivateMsg_t.c.id,
	'description' : OAPrivateMsg_t.c.description,
	'createTime' : OAPrivateMsg_t.c.createTime,
	'user_id' : OAPrivateMsg_t.c.user_id,
})

mapper(OAPrivateTask, OAPrivateTask_t, properties = { 
	'id' : OAPrivateTask_t.c.id,
	'user_id' : OAPrivateTask_t.c.user_id,
	'name' : OAPrivateTask_t.c.name,
	'description' : OAPrivateTask_t.c.description,
	'createTime' : OAPrivateTask_t.c.createTime,
	'deadline' : OAPrivateTask_t.c.deadline,
	'finishTime' : OAPrivateTask_t.c.finishTime,
})

mapper(OAUser, OAUser_t, properties = {
	'id' : OAUser_t.c.id,
	'department_id' : OAUser_t.c.department_id,
	'privateMsgs' : relation(OAPrivateMsg, backref = 'OAUser'),
	'privateTasks' : relation(OAPrivateTask, backref = 'OAUser'),
})

mapper(Profession, Profession_t, properties = {
	'id' : Profession_t.c.id,
	'name' : Profession_t.c.name,
})

mapper(SignUpEmail, SignUpEmail_t, properties = {
	'id' : SignUpEmail_t.c.id,
	'email' : SignUpEmail_t.c.email,
	'authCode' : SignUpEmail_t.c.authCode,
	'updateTime' : SignUpEmail_t.c.updateTime
})

mapper(SignUpMobile, SignUpMobile_t, properties = {
	'id' : SignUpMobile_t.c.id,
	'mobile' : SignUpMobile_t.c.mobile,
	'authCode' : SignUpMobile_t.c.authCode,
	'updateTime' : SignUpMobile_t.c.updateTime
})

mapper(User, User_t, properties = {
	'id' : User_t.c.id,
	'name' : User_t.c.name, 
	'password' : User_t.c.password,
	'gender' : User_t.c.gender,
	'mobile' : User_t.c.mobile,
	'birthday' : User_t.c.birthday,
	'createTime' : User_t.c.createTime,
	'fidoPoints' : User_t.c.fidoPoints,
	'email' : User_t.c.email,
	'fidocard' : User_t.c.fidocard,
	'updateTime' : User_t.c.updateTime,
	'activeTime' : User_t.c.activeTime,
	'userGroups' : relation(UserGroup, secondary = User_UserGroup_t, backref = 'users'),
	'schoolMapLocTopics' : relation(SchoolMapLocTopic, backref = 'user'),
	'schoolMapLocTopicPosts' : relation(SchoolMapLocTopicPost, backref = 'user'),
	'notifications' : relation(UserNotification, backref = 'user'),
})

mapper(UserGroup, UserGroup_t, properties = {
	'id' : UserGroup_t.c.id,
	'name' : UserGroup_t.c.name,
	'description' : UserGroup_t.c.description,
	'parent_id' : UserGroup_t.c.parent_id,
})

mapper(UserNotification, UserNotification_t, properties = {
	'id' : UserNotification_t.c.id,
	'user_id' : UserNotification_t.c.user_id,
	'additionalId' : UserNotification_t.c.additionalId,
	'repeatedTimes' : UserNotification_t.c.repeatedTimes,
	'type_id' : UserNotification_t.c.type_id,
	'createTime' : UserNotification_t.c.createTime,
	'updateTime' : UserNotification_t.c.updateTime,
	'content' : UserNotification_t.c.content,
})

mapper(UserNotificationType, UserNotificationType_t, properties = {
	'id' : UserNotificationType_t.c.id,
	'name' : UserNotificationType_t.c.name,
	'notifications' : relation(UserNotification, backref = 'type'),
})

mapper(User_UserGroup, User_UserGroup_t, properties = {
	'user_id' : User_UserGroup_t.c.user_id,
	'userGroup_id' : User_UserGroup_t.c.userGroup_id,
})